package Navigator.student;

import Worker.WorkerEnity;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;

//学生逻辑类
public class StudentDao {
    public Connection getCon(){
        Connection con=null;
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            String url="jdbc:mysql://localhost:3306/jwglxt?serverTimezone=UTC&characterEncoding=utf-8";
            con= DriverManager.getConnection(url,"root","root");
        }catch (Exception e){
            e.printStackTrace();
        }
        return con;
    }
    //依据学号查询
    public StudentEnity getStudent(String studentNo){
        Connection con=this.getCon();
        //定义sql语句
        String sql="select studentNo,studentName,sex,className,collage,studentPas from tb_student where studentNo=?";

        try {
            //创建prepareStatement
            PreparedStatement ps=con.prepareStatement(sql);
            ps.setString(1,studentNo);
            ResultSet rs=ps.executeQuery();//执行sql语句
            StudentEnity studentEnity=new StudentEnity();
            while (rs.next()) {
              studentEnity.setStudentNo(rs.getString("studentNo"));
              studentEnity.setStudentName(rs.getString("studentName"));
              studentEnity.setSex(rs.getString("sex"));
              studentEnity.setCollage(rs.getString("collage"));
              studentEnity.setClassName(rs.getString("className"));
              studentEnity.setStudentPas(rs.getString("studentPas"));
            }
            rs.close();
            ps.close();
            con.close();
            return studentEnity;
        }catch (Exception e){
            e.printStackTrace();
            return null;
        }

    }
    //新增学生
    public int addStudent(String studentNo,String studentName,String sex,String className,String collage,String studentPas){
        //创建con连接对象
        Connection con=this.getCon();
        //sql语句
        String sql="insert into tb_student (studentNo,studentName,sex,className,collage,studentPas) values(?,?,?,?,?,?)";
        //创建ps对象
        try {
            PreparedStatement ps=con.prepareStatement(sql);
            ps.setString(1,studentNo);
            ps.setString(2,studentName);
            ps.setString(3,sex);
            ps.setString(4,className);
            ps.setString(5,collage);
            ps.setString(6,studentPas);
            //执行sql语句
            int row=ps.executeUpdate();
            return row;
        }catch (Exception e){
            e.printStackTrace();
            return 0;
        }
    }
    //修改学生信息
    public int updateStudent(String studentNo,String studentName,String sex,String className,String collage,String studentPas){
        Connection con=this.getCon();
        try {
            //sql语句
            String sql="update tb_student set studentName=?,sex=?,className=?,collage=?,studentPas=? where studentNo=?";
            PreparedStatement ps=con.prepareStatement(sql);
            ps.setString(1,studentName);
            ps.setString(2,sex);
            ps.setString(3,className);
            ps.setString(4,collage);
            ps.setString(5,studentPas);
            ps.setString(6,studentNo);
            //执行sql语句
            int row=ps.executeUpdate();
            ps.close();
            con.close();
            return row;
        }catch (Exception e){
            e.printStackTrace();
        }
        return 0;
    }
    //删除学生
    public int deleteStudent(String studentNo){
        //创建数据库连接对象
        Connection con=this.getCon();
        //sql语句
        String sql="delete from tb_student where studentNo=?";
        try {
            //创建PS对象
            PreparedStatement ps=con.prepareStatement(sql);
            ps.setString(1,studentNo);
            //执行sql
            int row=ps.executeUpdate();
            ps.close();
            con.close();
            return row;
        }catch (Exception e){
            e.printStackTrace();
        }
        return 0;
    }
    //登录
    public String getLogin(String studentNo,String studentPas){
        Connection con=this.getCon();
        try {
            //sql语句
            String sql="select studentNo from tb_student where studentNo=? and studentPas=?";
            //ps对象
            PreparedStatement ps=con.prepareStatement(sql);
            ps.setString(1,studentNo);
            ps.setString(2,studentPas);
            ResultSet rs=ps.executeQuery();
            String studentNo1=null;
            //获得数据
            while (rs.next()){
                 studentNo1=rs.getString("studentNo");
            }
            rs.close();
            ps.close();
            con.close();
            return studentNo1;
        }catch (Exception e){
            e.printStackTrace();
            return null;
        }
    }
    //根据班级名查询学生学号
    public ArrayList<StudentEnity> getStudentNoByClass(String className){
        Connection con=this.getCon();
        ArrayList<StudentEnity> list=new ArrayList<>();
        //添加学生
        try {
            //sql语句
            String sql="select studentNo from tb_student where className=?";
            //创建ps对象
            PreparedStatement ps=con.prepareStatement(sql);
            ps.setString(1,className);
            //执行sql语句
            ResultSet rs=ps.executeQuery();
            //结束
            while (rs.next()){
                StudentEnity studentEnity=new StudentEnity();
                studentEnity.setStudentNo(rs.getString("studentNo"));
                list.add(studentEnity);
            }
            rs.close();
            ps.close();
            con.close();
            return list;
        }catch (Exception e){
            e.printStackTrace();
            return null;
        }
    }


}
